﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
using BabyShop.DBO;

namespace BabyShop
{    
    class DB_Provider
    {
        //static string conn_str = "Provider=SQLOLEDB;Data Source=Hung\\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=DeAnDB";
        public static string conn_str = WebConfigurationManager.ConnectionStrings["myconn"].ToString();
        public static DataTable Read(string query)
        {
            DataTable dt = new DataTable();            
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(query, conn_str);                
                da.FillSchema(dt,SchemaType.Source);
                da.Fill(dt);                
            }
            catch (Exception e)
            {
                Console.Write(e.ToString());
            }
            return dt;
        }

        public static DataTable ReadSchema(string query)
        {
            DataTable dt = new DataTable();            
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(query, conn_str);
                da.FillSchema(dt,SchemaType.Source);                
            }
            catch (Exception e)
            {
                e.ToString();
            }
            return dt;
        }

        public static int Write(DataTable dt, string table_name)
        {
            int kq = 0;
            string query = "Select * FROM " + table_name;
            dt.Columns[0].ReadOnly = false;            
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(query, conn_str);
                new SqlCommandBuilder(da);
                da.RowUpdated += new SqlRowUpdatedEventHandler(Update_Identity);
                kq = da.Update(dt);
            }
            catch (Exception e)
            {
                e.ToString();
            }
            return kq;
        }

        public static int WriteMulti(DataTable dt, string query)
        {
            int kq = 0;            
            dt.Columns[0].ReadOnly = false;
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(query, conn_str);
                new SqlCommandBuilder(da);
                da.RowUpdated += new SqlRowUpdatedEventHandler(Update_Identity);
                kq = da.Update(dt);
            }
            catch (Exception e)
            {
                e.ToString();
            }
            return kq;
        }

        public static void Update_Identity(object sender, SqlRowUpdatedEventArgs e)
        {
            SqlConnection conn = e.Command.Connection;
            if (e.StatementType == StatementType.Insert)
            {
                SqlCommand cmd = new SqlCommand("Select @@IDENTITY",conn);
                int ma = (int)cmd.ExecuteScalar();                
                try
                {
                    e.Row.BeginEdit();
                    e.Row[0] = ma;
                    e.Row.EndEdit();
                }
                catch (Exception ex)
                {
                    ex.ToString();
                }
            }
        }
        public static int Exec_Commnad(string query)
        {            
            SqlConnection conn = new SqlConnection(conn_str);
            conn.Open();
            SqlCommand cmd = new SqlCommand(query,conn);
            int kq = (int)cmd.ExecuteNonQuery();
            conn.Close();
            return kq;
        }

        public static int Get(string query)
        {
            SqlConnection conn = new SqlConnection(conn_str);
            conn.Open();
            SqlCommand cmd = new SqlCommand(query, conn);
            int kq = (int)cmd.ExecuteScalar();
            conn.Close();
            return kq;
        }

        public static SqlDataAdapter getAdapter(string sql)
        {
            SqlDataAdapter da = new SqlDataAdapter(sql, DB_Provider.conn_str);
            new SqlCommandBuilder(da);
            return da;
        }
    }
}